package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ScoreDTO {
	ScoreVo sv;
	Connection conn;
	// 페이지 넘기기 기능의 변수들
	int listsize=10; // 페이지당 표시되는 행수
	int blocksize=3; // 한블럭당 표시되는 페이지수
	int totsize=0; //전체 게시물의 갯수
	int totpage=0; //전체 페이지수
	int totblock=0; //전체 블럭수
	int nowpage=1; //현재 페이지
	int nowblock=1; //현재 블럭
	int startno=0; //리스트 목록의 시작 위치
	int endno=0; //리스트 목록의 끝 위치
	int startpage=0; //현재 블럭에서 표시되는 시작 블럭번호
	int endpage=0; // 현재 블럭에서 표시되는 끝 블럭 번호
	
	public ScoreDTO()
	{
		conn = new MyOracle().getConn();
	}

	public ScoreVo getSv() {
		return sv;
	}

	public void setSv(ScoreVo sv) {
		this.sv = sv;
		this.nowpage = sv.getNowpage();
		this.nowblock = sv.getNowblock();
	}
	
	public boolean addscore()
	{
		boolean flag=false;
		try
		{
			int n;
			String sql = "insert into score values(?, ?, ?, ?, ?, ?, ?, seq_score_rno.nextval)";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, sv.getGrade());
			pst.setInt(2, sv.getPoint());
			pst.setString(3, sv.getScode());
			pst.setString(4, sv.getMid());
			pst.setString(5, sv.getExamdate());
			pst.setString(6, sv.getExam());
			pst.setInt(7, sv.getRank());
			n = pst.executeUpdate();
			if(n>0)
			{
				flag=true;
			}
			pst.close();
			conn.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return flag;
	}
	public void page()
	{
		String sql = "select count(*) cnt from score where mid like ? or exam like ?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+sv.getFind()+"%");
			pst.setString(2, "%"+sv.getFind()+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				totsize = rs.getInt("cnt");
				totpage = (int)Math.ceil(totsize*1.0 / listsize);
				totblock = (int)Math.ceil(totpage*1.0 / blocksize);
				nowblock = (int)Math.ceil(nowpage*1.0 / blocksize);
				endpage = nowblock * blocksize;
				startpage = endpage - blocksize+1;
				endno = nowpage * listsize;
				startno = endno - listsize +1;
				if(startpage < 1)
				{
					startpage = 1;
				}
				if(endpage > totpage)
				{
					endpage = totpage;
				}
				if(endno > totsize)
				{
					endno = totsize;
				}
			}
//			System.out.println("totsize = "+totsize+"\n");
//			System.out.println("totpage = "+totpage+"\n");
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public ArrayList<ScoreVo> select()
	{
		page();
		ArrayList<ScoreVo> al = new ArrayList<ScoreVo>();
		String sql = "select * from(select rownum rn, c.* from(select * from score where mid like ? or exam like ? order by rno)c)b where b.rn between ? and ?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+sv.getFind()+"%");
			pst.setString(2, "%"+sv.getFind()+"%");
			pst.setInt(3, startno);
			pst.setInt(4, endno);
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				ScoreVo v = new ScoreVo();
				v.setGrade(rs.getInt("grade"));
				v.setPoint(rs.getInt("point"));
				v.setScode(rs.getString("scode"));
				v.setMid(rs.getString("mid"));
				v.setExamdate(rs.getString("examdate"));
				v.setExam(rs.getString("exam"));
				v.setRank(rs.getInt("rank"));
				v.setRno(rs.getInt("rno"));
				al.add(v);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return al;
	}
	public ScoreVo selectOne()
	{
		ScoreVo v = new ScoreVo();
		String sql = "select * from score where rno=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, sv.getRno());
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				v.setGrade(rs.getInt("grade"));
				v.setPoint(rs.getInt("point"));
				v.setScode(rs.getString("scode"));
				v.setMid(rs.getString("mid"));
				v.setExamdate(rs.getString("examdate"));
				v.setExam(rs.getString("exam"));
				v.setRank(rs.getInt("rank"));
				v.setRno(rs.getInt("rno"));
			}
			rs.close();
			pst.close();
			conn.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return v;
	}
	public boolean delete()
	{
		boolean flag=false;
		String sql = "delete from score where rno=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, sv.getRno());
			int cnt = pst.executeUpdate();
			if(cnt>0)
			{
				flag=true;
			}
			pst.close();
			conn.close();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return flag;
	}

	public int getListsize() {
		return listsize;
	}

	public void setListsize(int listsize) {
		this.listsize = listsize;
	}

	public int getBlocksize() {
		return blocksize;
	}

	public void setBlocksize(int blocksize) {
		this.blocksize = blocksize;
	}

	public int getTotsize() {
		return totsize;
	}

	public void setTotsize(int totsize) {
		this.totsize = totsize;
	}

	public int getTotpage() {
		return totpage;
	}

	public void setTotpage(int totpage) {
		this.totpage = totpage;
	}

	public int getTotblock() {
		return totblock;
	}

	public void setTotblock(int totblock) {
		this.totblock = totblock;
	}

	public int getNowpage() {
		return nowpage;
	}

	public void setNowpage(int nowpage) {
		this.nowpage = nowpage;
	}

	public int getNowblock() {
		return nowblock;
	}

	public void setNowblock(int nowblock) {
		this.nowblock = nowblock;
	}

	public int getStartno() {
		return startno;
	}

	public void setStartno(int startno) {
		this.startno = startno;
	}

	public int getEndno() {
		return endno;
	}

	public void setEndno(int endno) {
		this.endno = endno;
	}

	public int getStartpage() {
		return startpage;
	}

	public void setStartpage(int startpage) {
		this.startpage = startpage;
	}

	public int getEndpage() {
		return endpage;
	}

	public void setEndpage(int endpage) {
		this.endpage = endpage;
	}
	
}
